﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace Ko2.Portal
{
    public class Env
    {

        #region Members

        private string _AxaptaConnStr = "";
        public string AxaptaConnStr
        {
            get
            {
                _AxaptaConnStr = GetAppSetting(@"AXAPTAConnect");
                return _AxaptaConnStr;
            }
        }
        private string _DB04ConnStr = "";
        public string DB04ConnStr
        {
            get
            {
                _DB04ConnStr = GetAppSetting(@"DB04Connect");
                return _DB04ConnStr;
            }
        }
        private string _DBMySQLMainConnStr = "";
        public string DBMySQLMainConnStr
        {
            get
            {
                _DBMySQLMainConnStr = GetAppSetting(@"MySqlMainSrvConnect");
                return _DBMySQLMainConnStr;
            }
        }
        private string _DB1CConnStr = "";
        public string DB1CConnStr
        {
            get
            {
                _DB1CConnStr = GetAppSetting(@"1CConnect");
                return _DB1CConnStr;
            }
        }
        private string _DBBIConnStr = "";
        public string DBBIConnStr
        {
            get
            {
                _DBBIConnStr = GetAppSetting(@"BIConnect");
                return _DBBIConnStr;
            }
        }
        private string _DBApp10ConnStr = "";
        public string DBApp10ConnStr
        {
            get
            {
                _DBApp10ConnStr = GetAppSetting(@"APP10Connect");
                return _DBApp10ConnStr;
            }
        }

        #endregion

        #region App Settings
        public string GetAppSetting(String mName)
        {
            String vStr = null;
            SPWebService mSer = SPWebService.ContentService;
            #region Ver 1
            if (mSer != null)
            {
                foreach (SPWebConfigModification mMod in mSer.WebConfigModifications)
                {
                    if (mMod.Owner == mName)
                        vStr = mMod.Value;
                }
            }
            if (vStr != null)
            {
                vStr =
                    vStr.Substring(
                        vStr.IndexOf("value=") + 7
                        , vStr.Length - 7 - vStr.IndexOf("value=") - 4);
            }
            #endregion Ver 1
            else
            #region Ver 2
            {
                string strPath = "";
                string strPath1 =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
                string strPath2 =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\s-wss01.korablik.local80\web.config";
                string strPath3 =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\s-wss02.korablik.local80\web.config";
                string strPath4 =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\ScanDocs.korablik.local\web.config";

                if (System.IO.File.Exists(strPath1))
                    strPath = strPath1;
                else if (System.IO.File.Exists(strPath4))
                    strPath = strPath4;
                else if (System.IO.File.Exists(strPath2))
                    strPath = strPath2;
                else if (System.IO.File.Exists(strPath3))
                    strPath = strPath3;

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(strPath);
                XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
                foreach (XmlNode keynode in node.ChildNodes)
                {
                    string keyStr = keynode.Attributes["key"].Value;
                    if (keyStr == mName)
                    {
                        vStr = keynode.Attributes["value"].Value;
                        break;
                    }
                }
            }
            #endregion Ver 2
            if (String.IsNullOrEmpty(vStr))
                vStr = "";
            return vStr;
        }
        #endregion App Settings

        #region Web Settings

        #region Common
        public Exception GetWebSetting(ref string parRet, string parTitle, string parKey, string parSubKey)
        {
            Exception ret = null;
            if (string.IsNullOrEmpty(parRet))
                parRet = string.Empty;
            string s = "";
            string SettingsCamlStr = string.Format(
@"
<Where>
    <And>
        <And>
        <Eq>
            <FieldRef Name='Title' />
            <Value Type='Text'>{0}</Value>
        </Eq>
        <Eq>
            <FieldRef Name='Key' />
            <Value Type='Text'>{1}</Value>
        </Eq>
        </And>
        <Eq>
        <FieldRef Name='SubKey' />
        <Value Type='Text'>{2}</Value>
        </Eq>
    </And>
</Where>
", parTitle, parKey, parSubKey);
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                try
                {
                    using (SPSite site = new SPSite("http://localhost"))
                    {
                        using (SPWeb web = site.OpenWeb())
                        {
                            SPList list = web.Lists["Settings"];
                            SPQuery qry = new SPQuery();
                            qry.Query = SettingsCamlStr;
                            qry.RowLimit = 1;
                            foreach (SPListItem item in list.GetItems(qry))
                            {
                                if (item["Title"].ToString() == parTitle
                                    && item["Key"].ToString() == parKey
                                    && item["SubKey"].ToString() == parSubKey)
                                {
                                    s = item["Value"].ToString();
                                    break;
                                }
                            }

                        }
                    }
                }
                catch (Exception ex)
                {
                    ret = new Exception(ex.Message, ex);
                }
            });
            if (ret == null)
                parRet = s;
            return ret;
        }
        public Exception SetWebSetting(string parTitle, string parKey, string parSubKey, string parNewValue)
        {
            Exception ret = null;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                string SettingsCamlStr = string.Format(
@"
<Where>
    <And>
        <And>
        <Eq>
            <FieldRef Name='Title' />
            <Value Type='Text'>{0}</Value>
        </Eq>
        <Eq>
            <FieldRef Name='Key' />
            <Value Type='Text'>{1}</Value>
        </Eq>
        </And>
        <Eq>
        <FieldRef Name='SubKey' />
        <Value Type='Text'>{2}</Value>
        </Eq>
    </And>
</Where>
", parTitle, parKey, parSubKey);
                try
                {
                    using (SPSite site = new SPSite("http://localhost"))
                    {
                        using (SPWeb web = site.OpenWeb())
                        {
                            SPList list = web.Lists["Settings"];
                            SPQuery qry = new SPQuery();
                            qry.Query = SettingsCamlStr;
                            qry.RowLimit = 1;
                            foreach (SPListItem item in list.GetItems(qry))
                            {
                                if (item["Title"].ToString() == parTitle
                                    && item["Key"].ToString() == parKey
                                    && item["SubKey"].ToString() == parSubKey)
                                {
                                    item["Value"] = parNewValue;
                                    item.SystemUpdate();
                                    item.Update();
                                    break;
                                }
                            }

                        }
                    }
                }
                catch (Exception ex)
                {
                    ret = new Exception(ex.Message, ex);
                }
            });
            return ret;
        }
        #endregion
        #region Catalog
        public Exception GetWebSetting_Shops_LastSuccessfully(ref DateTime parDT)
        {
            string parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr
                , "Job-CreateShops"
                , "LastSuccessfully"
                , "DateTime");
            if (ret != null)
                return ret;
            try
            {
                parDT = DateTime.Parse(parStr);
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception SetWebSetting_Shops_LastSuccessfully(DateTime parNewDT)
        {
            string parStr = parNewDT.ToString();
            Exception ret = SetWebSetting(
                  "Job-CreateShops"
                , "LastSuccessfully"
                , "DateTime"
                , parStr);
            return ret;
        }
        public Exception GetWebSetting_Suplier_LastSuccessfully(ref DateTime parDT)
        {
            string parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr
                , "Job-CreateSuplier"
                , "LastSuccessfully"
                , "DateTime");
            if (ret != null)
                return ret;
            try
            {
                parDT = DateTime.Parse(parStr);
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception SetWebSetting_Suplier_LastSuccessfully(DateTime parNewDT)
        {
            string parStr = parNewDT.ToString();
            Exception ret = SetWebSetting(
                  "Job-CreateSuplier"
                , "LastSuccessfully"
                , "DateTime"
                , parStr);
            return ret;
        }
        #endregion
        #region PermDocs
        public Exception GetWebSetting_CreateInDoc_LastSuccessfully(ref DateTime parDT)
        {
            string parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr
                , "Job-CreateInDocs"
                , "LastSuccessfully"
                , "DateTime");
            if (ret != null)
                return ret;
            try
            {
                parDT = DateTime.Parse(parStr);
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception SetWebSetting_CreateInDoc_LastSuccessfully(DateTime parNewDT)
        {
            string parStr = parNewDT.ToString();
            Exception ret = SetWebSetting(
                  "Job-CreateInDocs"
                , "LastSuccessfully"
                , "DateTime"
                , parStr);
            return ret;
        }
        #endregion
        #region TillDocs
        public Exception GetWebSetting_PossDocs_KM3_LastSuccessfully(ref DateTime parDT)
        {
            string parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr
                , "Job-PossDocs-KM3"
                , "LastSuccessfully"
                , "DateTime");
            if (ret != null)
                return ret;
            try
            {
                parDT = DateTime.Parse(parStr);
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception SetWebSetting_PossDocs_KM3_LastSuccessfully(DateTime parNewDT)
        {
            string parStr = parNewDT.ToString();
            Exception ret = SetWebSetting(
                  "Job-PossDocs-KM3"
                , "LastSuccessfully"
                , "DateTime"
                , parStr);
            return ret;
        }
        public Exception GetWebSetting_PossDocs_KM6_LastSuccessfully(ref DateTime parDT)
        {
            string parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr
                , "Job-PossDocs-KM6"
                , "LastSuccessfully"
                , "DateTime");
            if (ret != null)
                return ret;
            try
            {
                parDT = DateTime.Parse(parStr);
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception SetWebSetting_PossDocs_KM6_LastSuccessfully(DateTime parNewDT)
        {
            string parStr = parNewDT.ToString();
            Exception ret = SetWebSetting(
                  "Job-PossDocs-KM6"
                , "LastSuccessfully"
                , "DateTime"
                , parStr);
            return ret;
        }
        public Exception GetWebSetting_DocAttr_OrgAddr_Line1(ref string parStr)
        {
            parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr, "DocAttr", "OrgAddr", "Line1");
            return ret;
        }
        public Exception SetWebSetting_DocAttr_OrgAddr_Line1(string parStr)
        {
            Exception ret = SetWebSetting("DocAttr", "OrgAddr", "Line1", parStr);
            return ret;
        }
        public Exception GetWebSetting_DocAttr_OrgAddr_Line2(ref string parStr)
        {
            parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr, "DocAttr", "OrgAddr", "Line2");
            return ret;
        }
        public Exception SetWebSetting_DocAttr_OrgAddr_Line2(string parStr)
        {
            Exception ret = SetWebSetting("DocAttr", "OrgAddr", "Line2", parStr);
            return ret;
        }
        public Exception GetWebSetting_DocAttr_OKPO(ref string parStr)
        {
            parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr, "DocAttr", "OKPO", "Text");
            return ret;
        }
        public Exception SetWebSetting_DocAttr_OKPO(string parStr)
        {
            Exception ret = SetWebSetting("DocAttr", "OKPO", "Text", parStr);
            return ret;
        }
        public Exception GetWebSetting_DocAttr_INN(ref string parStr)
        {
            parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr, "DocAttr", "INN", "Text");
            return ret;
        }
        public Exception SetWebSetting_DocAttr_INN(string parStr)
        {
            Exception ret = SetWebSetting("DocAttr", "INN", "Text", parStr);
            return ret;
        }
        public Exception GetWebSetting_DocAttr_OKDP(ref string parStr)
        {
            parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr, "DocAttr", "OKDP", "Text");
            return ret;
        }
        public Exception SetWebSetting_DocAttr_OKDP(string parStr)
        {
            Exception ret = SetWebSetting("DocAttr", "OKDP", "Text", parStr);
            return ret;
        }
        #endregion
        #region 
        public Exception GetWebSetting_ShopsDiscussion_LastSuccessfully(ref DateTime parDT)
        {
            string parStr = string.Empty;
            Exception ret = GetWebSetting(ref parStr
                , "Job-CreateShopsDiscussion"
                , "LastSuccessfully"
                , "DateTime");
            if (ret != null)
                return ret;
            try
            {
                parDT = DateTime.Parse(parStr);
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }
        public Exception SetWebSetting_ShopsDiscussion_LastSuccessfully(DateTime parNewDT)
        {
            string parStr = parNewDT.ToString();
            Exception ret = SetWebSetting(
                  "Job-CreateShopsDiscussion"
                , "LastSuccessfully"
                , "DateTime"
                , parStr);
            return ret;
        }
        #endregion

        #endregion Web Settings

        #region DB

        protected Exception GetTable(ref DataTable tbl, string ConnStr, string sql)
        {
            Exception ret = null;
            if (tbl == null) tbl = new DataTable();
            try
            {
                using (OleDbConnection cn = new OleDbConnection(ConnStr))
                {
                    cn.Open();
                    using (OleDbCommand myCMD = new OleDbCommand(sql, cn))
                    {
                        myCMD.CommandType = CommandType.Text;
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                        {
                            adapter.Fill(tbl);
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                ret = new Exception(ex.Message, ex);
            }
            return ret;
        }

        #endregion

        #region Verify

        public Exception VerifyShopsNames(ref DataTable tbl, string colName)
        {
            Exception ret = null;
            if (tbl == null)
                return ret;
            DataTable namesTbl = new DataTable();
            ret = GetVerifyShopsNamesTable(ref namesTbl);
            if (ret != null)
                return ret;

            for (int i = 0; i < namesTbl.Rows.Count; i++)
            {
                DataRow r = namesTbl.Rows[i];
                string app10name = r["App10Name"].ToString();
                foreach (DataRow rr in tbl.Rows)
                {
                    string srcName = rr[colName].ToString();
                    if (srcName == app10name)
                        rr[colName] = r["AxName"];
                }
            }

            return ret;
        }

        public Exception GetVerifyShopsNamesTable(ref DataTable tbl)
        {
            string ConnStr = GetAppSetting("DB04Connect");
            string sql =
@"SELECT AxName, App10Name FROM ShopsNameVerify AS tbl ORDER BY AxName";
            return GetTable(ref tbl, ConnStr, sql);
        }

        #endregion

        #region Logs
        public Exception Log_Add(string parName, string parKey, string parSubKey, string parValue)
        {
            Exception ret = new SP_Log().Insert(parName, parKey, parSubKey, parValue);
            return ret;
        }
        public Exception Log_Add(string parName, string parKey, string parSubKey, string parValue, string parMsg, int parErrId, int parWarningLevel, int parRetCode, int parCountInfo)
        {
            Exception ret = new SP_Log().Insert(parName, parKey, parSubKey, parValue, parMsg, parErrId, parWarningLevel, parRetCode, parCountInfo);
            return ret;
        }
        #endregion Logs

    }
}
